{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## groupby"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>data1</th>\n",
       "      <th>data2</th>\n",
       "      <th>key1</th>\n",
       "      <th>key2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>-1.304072</td>\n",
       "      <td>0.602548</td>\n",
       "      <td>a</td>\n",
       "      <td>one</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.892170</td>\n",
       "      <td>-0.386406</td>\n",
       "      <td>a</td>\n",
       "      <td>two</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.457713</td>\n",
       "      <td>-1.045518</td>\n",
       "      <td>b</td>\n",
       "      <td>one</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1.123721</td>\n",
       "      <td>-0.057502</td>\n",
       "      <td>b</td>\n",
       "      <td>two</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>-1.136373</td>\n",
       "      <td>1.305967</td>\n",
       "      <td>a</td>\n",
       "      <td>one</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      data1     data2 key1 key2\n",
       "0 -1.304072  0.602548    a  one\n",
       "1  0.892170 -0.386406    a  two\n",
       "2  0.457713 -1.045518    b  one\n",
       "3  1.123721 -0.057502    b  two\n",
       "4 -1.136373  1.305967    a  one"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame({'key1':list('aabba'),\n",
    "                  'key2': ['one','two','one','two','one'],\n",
    "                  'data1': np.random.randn(5),\n",
    "                  'data2': np.random.randn(5)})\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>data1</th>\n",
       "      <th>data2</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>key1</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>a</th>\n",
       "      <td>-0.516091</td>\n",
       "      <td>0.50737</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>0.790717</td>\n",
       "      <td>-0.55151</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         data1    data2\n",
       "key1                   \n",
       "a    -0.516091  0.50737\n",
       "b     0.790717 -0.55151"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped = df.groupby(df['key1'])\n",
    "grouped.mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "key1\n",
       "a   -0.516091\n",
       "b    0.790717\n",
       "Name: data1, dtype: float64"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped = df['data1'].groupby(df['key1'])\n",
    "grouped.mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>data1</th>\n",
       "      <th>data2</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>key1</th>\n",
       "      <th>key2</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">a</th>\n",
       "      <th>one</th>\n",
       "      <td>-1.220222</td>\n",
       "      <td>0.954258</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>two</th>\n",
       "      <td>0.892170</td>\n",
       "      <td>-0.386406</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">b</th>\n",
       "      <th>one</th>\n",
       "      <td>0.457713</td>\n",
       "      <td>-1.045518</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>two</th>\n",
       "      <td>1.123721</td>\n",
       "      <td>-0.057502</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              data1     data2\n",
       "key1 key2                    \n",
       "a    one  -1.220222  0.954258\n",
       "     two   0.892170 -0.386406\n",
       "b    one   0.457713 -1.045518\n",
       "     two   1.123721 -0.057502"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 按多个键分组\n",
    "grouped = df.groupby([df['key1'],df['key2']])\n",
    "grouped.mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "California  2005    0.892170\n",
       "            2006    0.457713\n",
       "Ohio        2005   -0.090175\n",
       "            2006   -1.136373\n",
       "Name: data1, dtype: float64"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 以上的分组键均为Series，实际上分组键可以是任何长度适当的数组\n",
    "states=np.array(['Ohio','California','California','Ohio','Ohio'])\n",
    "years=np.array([2005,2005,2006,2005,2006])\n",
    "df['data1'].groupby([states,years]).mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>data1</th>\n",
       "      <th>data2</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>key1</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>a</th>\n",
       "      <td>-0.516091</td>\n",
       "      <td>0.50737</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>0.790717</td>\n",
       "      <td>-0.55151</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         data1    data2\n",
       "key1                   \n",
       "a    -0.516091  0.50737\n",
       "b     0.790717 -0.55151"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 可以看出没有key2列，因为df[‘key2’]不是数值数据，所以被从结果中移除。\n",
    "# 默认情况下，所有数值列都会被聚合，虽然有时可能被过滤为一个子集。\n",
    "df.groupby('key1').mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 对分组进行迭代"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "a\n",
      "      data1     data2 key1 key2\n",
      "0 -1.304072  0.602548    a  one\n",
      "1  0.892170 -0.386406    a  two\n",
      "4 -1.136373  1.305967    a  one\n",
      "b\n",
      "      data1     data2 key1 key2\n",
      "2  0.457713 -1.045518    b  one\n",
      "3  1.123721 -0.057502    b  two\n"
     ]
    }
   ],
   "source": [
    "for name, group in df.groupby('key1'):\n",
    "    print(name)\n",
    "    print(group)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "===k1,k2:\n",
      "a one\n",
      "===k3:\n",
      "      data1     data2 key1 key2\n",
      "0 -1.304072  0.602548    a  one\n",
      "4 -1.136373  1.305967    a  one\n",
      "===k1,k2:\n",
      "a two\n",
      "===k3:\n",
      "     data1     data2 key1 key2\n",
      "1  0.89217 -0.386406    a  two\n",
      "===k1,k2:\n",
      "b one\n",
      "===k3:\n",
      "      data1     data2 key1 key2\n",
      "2  0.457713 -1.045518    b  one\n",
      "===k1,k2:\n",
      "b two\n",
      "===k3:\n",
      "      data1     data2 key1 key2\n",
      "3  1.123721 -0.057502    b  two\n"
     ]
    }
   ],
   "source": [
    "# name就是groupby中的key1的值，group就是要输出的内容。 \n",
    "# 同理：\n",
    "for (k1,k2),group in df.groupby(['key1','key2']):\n",
    "    print ('===k1,k2:')\n",
    "    print (k1,k2)\n",
    "    print ('===k3:')\n",
    "    print (group)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'a':       data1     data2 key1 key2\n",
       " 0 -1.304072  0.602548    a  one\n",
       " 1  0.892170 -0.386406    a  two\n",
       " 4 -1.136373  1.305967    a  one, 'b':       data1     data2 key1 key2\n",
       " 2  0.457713 -1.045518    b  one\n",
       " 3  1.123721 -0.057502    b  two}"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "piece=dict(list(df.groupby('key1')))\n",
    "piece"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{dtype('float64'):       data1     data2\n",
       " 0 -1.304072  0.602548\n",
       " 1  0.892170 -0.386406\n",
       " 2  0.457713 -1.045518\n",
       " 3  1.123721 -0.057502\n",
       " 4 -1.136373  1.305967, dtype('O'):   key1 key2\n",
       " 0    a  one\n",
       " 1    a  two\n",
       " 2    b  one\n",
       " 3    b  two\n",
       " 4    a  one}"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# groupby默认是在axis=0上进行分组的，通过设置也可以在其他任何轴上进行分组.\n",
    "\n",
    "grouped=df.groupby(df.dtypes, axis=1)\n",
    "dict(list(grouped))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 选取一个或者一组列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>data1</th>\n",
       "      <th>data2</th>\n",
       "      <th>key1</th>\n",
       "      <th>key2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>-1.304072</td>\n",
       "      <td>0.602548</td>\n",
       "      <td>a</td>\n",
       "      <td>one</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.892170</td>\n",
       "      <td>-0.386406</td>\n",
       "      <td>a</td>\n",
       "      <td>two</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.457713</td>\n",
       "      <td>-1.045518</td>\n",
       "      <td>b</td>\n",
       "      <td>one</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1.123721</td>\n",
       "      <td>-0.057502</td>\n",
       "      <td>b</td>\n",
       "      <td>two</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>-1.136373</td>\n",
       "      <td>1.305967</td>\n",
       "      <td>a</td>\n",
       "      <td>one</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      data1     data2 key1 key2\n",
       "0 -1.304072  0.602548    a  one\n",
       "1  0.892170 -0.386406    a  two\n",
       "2  0.457713 -1.045518    b  one\n",
       "3  1.123721 -0.057502    b  two\n",
       "4 -1.136373  1.305967    a  one"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "              data1     data2\n",
      "key1 key2                    \n",
      "a    one  -1.220222  0.954258\n",
      "     two   0.892170 -0.386406\n",
      "b    one   0.457713 -1.045518\n",
      "     two   1.123721 -0.057502\n",
      "key1  key2\n",
      "a     one     0.954258\n",
      "      two    -0.386406\n",
      "b     one    -1.045518\n",
      "      two    -0.057502\n",
      "Name: data2, dtype: float64\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>data2</th>\n",
       "      <th>data1</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>key1</th>\n",
       "      <th>key2</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">a</th>\n",
       "      <th>one</th>\n",
       "      <td>0.954258</td>\n",
       "      <td>-1.220222</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>two</th>\n",
       "      <td>-0.386406</td>\n",
       "      <td>0.892170</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">b</th>\n",
       "      <th>one</th>\n",
       "      <td>-1.045518</td>\n",
       "      <td>0.457713</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>two</th>\n",
       "      <td>-0.057502</td>\n",
       "      <td>1.123721</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              data2     data1\n",
       "key1 key2                    \n",
       "a    one   0.954258 -1.220222\n",
       "     two  -0.386406  0.892170\n",
       "b    one  -1.045518  0.457713\n",
       "     two  -0.057502  1.123721"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(df.groupby(['key1','key2']).mean())\n",
    "\n",
    "# 对于大数据，很多情况是只需要对部分列进行聚合\n",
    "print(df.groupby(['key1','key2'])['data2'].mean())\n",
    "df.groupby(['key1','key2'])['data2','data1'].mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 通过字典或者series进行分组"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "      <th>e</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Joe</th>\n",
       "      <td>0.836659</td>\n",
       "      <td>-0.155809</td>\n",
       "      <td>-0.260933</td>\n",
       "      <td>-0.479236</td>\n",
       "      <td>-0.747557</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Steve</th>\n",
       "      <td>-0.750791</td>\n",
       "      <td>-0.672957</td>\n",
       "      <td>-0.830508</td>\n",
       "      <td>0.835848</td>\n",
       "      <td>0.327191</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wes</th>\n",
       "      <td>0.935452</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.260250</td>\n",
       "      <td>1.839458</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jim</th>\n",
       "      <td>0.977290</td>\n",
       "      <td>-0.223177</td>\n",
       "      <td>1.648929</td>\n",
       "      <td>-0.895358</td>\n",
       "      <td>-1.537907</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Travis</th>\n",
       "      <td>0.838842</td>\n",
       "      <td>0.643993</td>\n",
       "      <td>0.130966</td>\n",
       "      <td>0.822127</td>\n",
       "      <td>0.882899</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               a         b         c         d         e\n",
       "Joe     0.836659 -0.155809 -0.260933 -0.479236 -0.747557\n",
       "Steve  -0.750791 -0.672957 -0.830508  0.835848  0.327191\n",
       "Wes     0.935452       NaN       NaN  0.260250  1.839458\n",
       "Jim     0.977290 -0.223177  1.648929 -0.895358 -1.537907\n",
       "Travis  0.838842  0.643993  0.130966  0.822127  0.882899"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "people=pd.DataFrame(np.random.randn(5,5),\n",
    "                   columns=list('abcde'),\n",
    "                   index=['Joe','Steve','Wes','Jim','Travis'])\n",
    "\n",
    "people.ix[2:3,['b','c']]=np.nan #设置几个nan\n",
    "people"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>blue</th>\n",
       "      <th>red</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Joe</th>\n",
       "      <td>-0.740169</td>\n",
       "      <td>-0.066708</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Steve</th>\n",
       "      <td>0.005340</td>\n",
       "      <td>-1.096557</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wes</th>\n",
       "      <td>0.260250</td>\n",
       "      <td>2.774910</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jim</th>\n",
       "      <td>0.753572</td>\n",
       "      <td>-0.783794</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Travis</th>\n",
       "      <td>0.953092</td>\n",
       "      <td>2.365734</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            blue       red\n",
       "Joe    -0.740169 -0.066708\n",
       "Steve   0.005340 -1.096557\n",
       "Wes     0.260250  2.774910\n",
       "Jim     0.753572 -0.783794\n",
       "Travis  0.953092  2.365734"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mapping={'a':'red','b':'red','c':'blue','d':'blue','e':'red','f':'orange'}\n",
    "by_column=people.groupby(mapping,axis=1)\n",
    "by_column.sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "      <th>e</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [a, b, c, d, e]\n",
       "Index: []"
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 如果不加axis=1, 则只会出现 a b c d e\n",
    "mapping={'a':'red','b':'red','c':'blue','d':'blue','e':'red','f':'orange'}\n",
    "by_column=people.groupby(mapping)\n",
    "by_column.sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "a       red\n",
      "b       red\n",
      "c      blue\n",
      "d      blue\n",
      "e       red\n",
      "f    orange\n",
      "dtype: object\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>blue</th>\n",
       "      <th>red</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Joe</th>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Steve</th>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wes</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jim</th>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Travis</th>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        blue  red\n",
       "Joe        2    3\n",
       "Steve      2    3\n",
       "Wes        1    2\n",
       "Jim        2    3\n",
       "Travis     2    3"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Series 也一样\n",
    "map_series=pd.Series(mapping)\n",
    "print(map_series)\n",
    "people.groupby(map_series,axis=1).count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 通过函数进行分组"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "               a         b         c         d         e\n",
      "Joe     0.836659 -0.155809 -0.260933 -0.479236 -0.747557\n",
      "Steve  -0.750791 -0.672957 -0.830508  0.835848  0.327191\n",
      "Wes     0.935452       NaN       NaN  0.260250  1.839458\n",
      "Jim     0.977290 -0.223177  1.648929 -0.895358 -1.537907\n",
      "Travis  0.838842  0.643993  0.130966  0.822127  0.882899\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "      <th>e</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2.749401</td>\n",
       "      <td>-0.378987</td>\n",
       "      <td>1.387996</td>\n",
       "      <td>-1.114343</td>\n",
       "      <td>-0.446006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>-0.750791</td>\n",
       "      <td>-0.672957</td>\n",
       "      <td>-0.830508</td>\n",
       "      <td>0.835848</td>\n",
       "      <td>0.327191</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>0.838842</td>\n",
       "      <td>0.643993</td>\n",
       "      <td>0.130966</td>\n",
       "      <td>0.822127</td>\n",
       "      <td>0.882899</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          a         b         c         d         e\n",
       "3  2.749401 -0.378987  1.387996 -1.114343 -0.446006\n",
       "5 -0.750791 -0.672957 -0.830508  0.835848  0.327191\n",
       "6  0.838842  0.643993  0.130966  0.822127  0.882899"
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 相较于dic或者Series，python函数在定义分组关系映射时更有创意。\n",
    "# 任何被当做分组键的函数都会在各个索引上被调用一次，其返回值就会被用作分组名称。\n",
    "# 假设你按人名的长度进行分组，仅仅传入len即可\n",
    "print(people)\n",
    "people.groupby(len).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "      <th>e</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">3</th>\n",
       "      <th>one</th>\n",
       "      <td>1.772111</td>\n",
       "      <td>-0.155809</td>\n",
       "      <td>-0.260933</td>\n",
       "      <td>-0.218986</td>\n",
       "      <td>1.091901</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>two</th>\n",
       "      <td>0.977290</td>\n",
       "      <td>-0.223177</td>\n",
       "      <td>1.648929</td>\n",
       "      <td>-0.895358</td>\n",
       "      <td>-1.537907</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <th>one</th>\n",
       "      <td>-0.750791</td>\n",
       "      <td>-0.672957</td>\n",
       "      <td>-0.830508</td>\n",
       "      <td>0.835848</td>\n",
       "      <td>0.327191</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <th>two</th>\n",
       "      <td>0.838842</td>\n",
       "      <td>0.643993</td>\n",
       "      <td>0.130966</td>\n",
       "      <td>0.822127</td>\n",
       "      <td>0.882899</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              a         b         c         d         e\n",
       "3 one  1.772111 -0.155809 -0.260933 -0.218986  1.091901\n",
       "  two  0.977290 -0.223177  1.648929 -0.895358 -1.537907\n",
       "5 one -0.750791 -0.672957 -0.830508  0.835848  0.327191\n",
       "6 two  0.838842  0.643993  0.130966  0.822127  0.882899"
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 将函数和数组、列表、字典、Series混合使用也不是问题，因为任何东西都会最终转换为数组\n",
    "key_list=['one','one','one','two','two'] \n",
    "people.groupby([len,key_list]).sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 根据索引级别进行分组"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th>cty</th>\n",
       "      <th colspan=\"3\" halign=\"left\">US</th>\n",
       "      <th colspan=\"2\" halign=\"left\">JP</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>tenor</th>\n",
       "      <th>1</th>\n",
       "      <th>3</th>\n",
       "      <th>5</th>\n",
       "      <th>1</th>\n",
       "      <th>3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.841506</td>\n",
       "      <td>-0.318683</td>\n",
       "      <td>-0.727548</td>\n",
       "      <td>-0.102212</td>\n",
       "      <td>1.135224</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.656744</td>\n",
       "      <td>-0.371614</td>\n",
       "      <td>-0.186148</td>\n",
       "      <td>-0.991809</td>\n",
       "      <td>0.944900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>-0.499534</td>\n",
       "      <td>0.083669</td>\n",
       "      <td>-0.464316</td>\n",
       "      <td>-0.131412</td>\n",
       "      <td>-1.253453</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>-0.496822</td>\n",
       "      <td>1.911844</td>\n",
       "      <td>-1.335649</td>\n",
       "      <td>0.436272</td>\n",
       "      <td>1.962672</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "cty          US                            JP          \n",
       "tenor         1         3         5         1         3\n",
       "0      0.841506 -0.318683 -0.727548 -0.102212  1.135224\n",
       "1      0.656744 -0.371614 -0.186148 -0.991809  0.944900\n",
       "2     -0.499534  0.083669 -0.464316 -0.131412 -1.253453\n",
       "3     -0.496822  1.911844 -1.335649  0.436272  1.962672"
      ]
     },
     "execution_count": 88,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 层次化索引最方便的地方就在于他能够根据索引级别进行聚合。\n",
    "# 要实现该目的，通过level关键字出入级别编号或者名称即可:\n",
    "columns=pd.MultiIndex.from_arrays([['US','US','US','JP','JP'],[1,3,5,1,3]],names=['cty','tenor'])\n",
    "hier_df=pd.DataFrame(np.random.randn(4,5),columns=columns)\n",
    "hier_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>cty</th>\n",
       "      <th>JP</th>\n",
       "      <th>US</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "cty  JP  US\n",
       "0     2   3\n",
       "1     2   3\n",
       "2     2   3\n",
       "3     2   3"
      ]
     },
     "execution_count": 89,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hier_df.groupby(level='cty',axis=1).count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 数据聚合"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 调用自定义的聚合函数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "      data1     data2 key1 key2\n",
      "0 -1.304072  0.602548    a  one\n",
      "1  0.892170 -0.386406    a  two\n",
      "2  0.457713 -1.045518    b  one\n",
      "3  1.123721 -0.057502    b  two\n",
      "4 -1.136373  1.305967    a  one\n",
      "===name:\n",
      "a\n",
      "===group:\n",
      "0   -1.304072\n",
      "1    0.892170\n",
      "4   -1.136373\n",
      "Name: data1, dtype: float64\n",
      "===name:\n",
      "b\n",
      "===group:\n",
      "2    0.457713\n",
      "3    1.123721\n",
      "Name: data1, dtype: float64\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "key1\n",
       "a    2.196242\n",
       "b    0.666009\n",
       "Name: data1, dtype: float64"
      ]
     },
     "execution_count": 110,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(df)\n",
    "grouped = df['data1'].groupby(df['key1'])\n",
    "for name,group in df['data1'].groupby(df['key1']):\n",
    "    print ('===name:')\n",
    "    print (name)\n",
    "    print ('===group:')\n",
    "    print (group)\n",
    "\n",
    "# 自定义函数\n",
    "def peak_to_peak(arr):\n",
    "    return arr.max()-arr.min()\n",
    "grouped.agg(peak_to_peak)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 面向列的多函数应用"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 常用函数  count、sum、mean、median、std、var(方差)、\n",
    "# min、max、prod（非NA值的积）、first（第一个非NA的值）、last"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 137,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "      data1     data2 key1 key2\n",
      "0 -1.304072  0.602548    a  one\n",
      "1  0.892170 -0.386406    a  two\n",
      "2  0.457713 -1.045518    b  one\n",
      "3  1.123721 -0.057502    b  two\n",
      "4 -1.136373  1.305967    a  one\n",
      "1*1*1*1*1*1*1*1*1*1*\n",
      "key1\n",
      "a   -0.516091\n",
      "b    0.790717\n",
      "Name: data1, dtype: float64\n",
      "2*2*2*2*2*2*2*2*2*2*\n",
      "          mean       std  peak_to_peak\n",
      "key1                                  \n",
      "a    -0.516091  1.222470      2.196242\n",
      "b     0.790717  0.470939      0.666009\n",
      "3*3*3*3*3*3*3*3*3*3*\n",
      "           0_+       0.0       =.=\n",
      "key1                              \n",
      "a    -0.516091  1.222470  1.222470\n",
      "b     0.790717  0.470939  0.470939\n",
      "4*4*4*4*4*4*4*4*4*4*\n",
      "          mean       std  peak_to_peak\n",
      "key1                                  \n",
      "a    -0.516091  1.222470      2.196242\n",
      "b     0.790717  0.470939      0.666009\n",
      "5*5*5*5*5*5*5*5*5*5*\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>data1</th>\n",
       "      <th>data2</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>key1</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>a</th>\n",
       "      <td>-0.516091</td>\n",
       "      <td>1.522109</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>0.790717</td>\n",
       "      <td>-1.103019</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         data1     data2\n",
       "key1                    \n",
       "a    -0.516091  1.522109\n",
       "b     0.790717 -1.103019"
      ]
     },
     "execution_count": 137,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 对Series或者DataFrame列的聚合运算实际是使用aggregate或者调用mean，std等方法。\n",
    "# 下面对不同的列使用不同的聚合函数，或者一次应用多个函数\n",
    "print(df)\n",
    "grouped = df['data1'].groupby(df['key1'])\n",
    "\n",
    "# 将函数名直接以字符串传入\n",
    "print(\"1*\"*10)\n",
    "print(grouped.agg('mean') )\n",
    "\n",
    "#如果传入一组函数，得到的df的列名就会以相应的函数命名\n",
    "print(\"2*\"*10)\n",
    "print(grouped.agg(['mean','std',peak_to_peak]) )\n",
    "\n",
    "# 如果传入的是(name, function)元组组成的列表，则各个元组的第一个元素将被用作df的列名\n",
    "print(\"3*\"*10)\n",
    "print(grouped.agg([('0_+','mean'),('0.0',np.std),('=.=',np.std)]) )\n",
    "\n",
    "# 对于df，可以定义一组用于全部列的函数,或在不同的列应用不同的函数 \n",
    "print(\"4*\"*10)\n",
    "functions = ['mean','std',peak_to_peak]\n",
    "print(grouped.agg(functions))\n",
    "\n",
    "# 如果想对不同的列应用不同的函数, 具体的办法是想agg传入一个从列名映射到函数的字典\n",
    "print(\"5*\"*10)\n",
    "grouped = df.groupby(df['key1'])\n",
    "functions = {'data1':'mean', 'data2':'sum'}\n",
    "grouped.agg(functions)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 分组级运算和转换"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 聚合只是分组运算的一种，它是数据转换的特列。transform 和apply更牛叉\n",
    "\n",
    "# transform会将一个函数应用到各个分组，然后将结果放在适当的位置. 如果各分组产生的标量值，则该标量值会被广播出去。\n",
    "# transform也是有严格条件的特殊函数：传入的函数只能产生两种结果，\n",
    "# 要么产生一个可以广播的标量值(如：np.mean), 要么产生一个相同大小的结果数组。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 148,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "      <th>e</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Joe</th>\n",
       "      <td>1.210137</td>\n",
       "      <td>0.547824</td>\n",
       "      <td>-0.128973</td>\n",
       "      <td>0.002002</td>\n",
       "      <td>-0.417625</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Steve</th>\n",
       "      <td>-0.392114</td>\n",
       "      <td>0.416604</td>\n",
       "      <td>-0.805733</td>\n",
       "      <td>-0.012115</td>\n",
       "      <td>0.791804</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wes</th>\n",
       "      <td>1.294789</td>\n",
       "      <td>0.518447</td>\n",
       "      <td>-0.037716</td>\n",
       "      <td>-1.561559</td>\n",
       "      <td>-0.319690</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jim</th>\n",
       "      <td>1.467127</td>\n",
       "      <td>-0.267567</td>\n",
       "      <td>-0.536224</td>\n",
       "      <td>0.058921</td>\n",
       "      <td>0.336582</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Travis</th>\n",
       "      <td>2.016760</td>\n",
       "      <td>-0.958386</td>\n",
       "      <td>-0.155097</td>\n",
       "      <td>0.869959</td>\n",
       "      <td>-0.595236</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               a         b         c         d         e\n",
       "Joe     1.210137  0.547824 -0.128973  0.002002 -0.417625\n",
       "Steve  -0.392114  0.416604 -0.805733 -0.012115  0.791804\n",
       "Wes     1.294789  0.518447 -0.037716 -1.561559 -0.319690\n",
       "Jim     1.467127 -0.267567 -0.536224  0.058921  0.336582\n",
       "Travis  2.016760 -0.958386 -0.155097  0.869959 -0.595236"
      ]
     },
     "execution_count": 148,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "people=pd.DataFrame(np.random.randn(5,5),\n",
    "                   columns=list('abcde'),\n",
    "                   index=['Joe','Steve','Wes','Jim','Travis'])\n",
    "people"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 149,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "      <th>e</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>one</th>\n",
       "      <td>1.507229</td>\n",
       "      <td>0.035962</td>\n",
       "      <td>-0.107262</td>\n",
       "      <td>-0.229866</td>\n",
       "      <td>-0.444184</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>two</th>\n",
       "      <td>0.537506</td>\n",
       "      <td>0.074519</td>\n",
       "      <td>-0.670979</td>\n",
       "      <td>0.023403</td>\n",
       "      <td>0.564193</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            a         b         c         d         e\n",
       "one  1.507229  0.035962 -0.107262 -0.229866 -0.444184\n",
       "two  0.537506  0.074519 -0.670979  0.023403  0.564193"
      ]
     },
     "execution_count": 149,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "key=['one','two','one','two','one']\n",
    "people.groupby(key).mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 150,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "      <th>e</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Joe</th>\n",
       "      <td>1.507229</td>\n",
       "      <td>0.035962</td>\n",
       "      <td>-0.107262</td>\n",
       "      <td>-0.229866</td>\n",
       "      <td>-0.444184</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Steve</th>\n",
       "      <td>0.537506</td>\n",
       "      <td>0.074519</td>\n",
       "      <td>-0.670979</td>\n",
       "      <td>0.023403</td>\n",
       "      <td>0.564193</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wes</th>\n",
       "      <td>1.507229</td>\n",
       "      <td>0.035962</td>\n",
       "      <td>-0.107262</td>\n",
       "      <td>-0.229866</td>\n",
       "      <td>-0.444184</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jim</th>\n",
       "      <td>0.537506</td>\n",
       "      <td>0.074519</td>\n",
       "      <td>-0.670979</td>\n",
       "      <td>0.023403</td>\n",
       "      <td>0.564193</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Travis</th>\n",
       "      <td>1.507229</td>\n",
       "      <td>0.035962</td>\n",
       "      <td>-0.107262</td>\n",
       "      <td>-0.229866</td>\n",
       "      <td>-0.444184</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               a         b         c         d         e\n",
       "Joe     1.507229  0.035962 -0.107262 -0.229866 -0.444184\n",
       "Steve   0.537506  0.074519 -0.670979  0.023403  0.564193\n",
       "Wes     1.507229  0.035962 -0.107262 -0.229866 -0.444184\n",
       "Jim     0.537506  0.074519 -0.670979  0.023403  0.564193\n",
       "Travis  1.507229  0.035962 -0.107262 -0.229866 -0.444184"
      ]
     },
     "execution_count": 150,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "people.groupby(key).transform(np.mean)\n",
    "# 可以看到有很多与表2一样的值。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 151,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "      <th>e</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Joe</th>\n",
       "      <td>-0.297092</td>\n",
       "      <td>0.511863</td>\n",
       "      <td>-0.021711</td>\n",
       "      <td>0.231868</td>\n",
       "      <td>0.026559</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Steve</th>\n",
       "      <td>-0.929621</td>\n",
       "      <td>0.342086</td>\n",
       "      <td>-0.134754</td>\n",
       "      <td>-0.035518</td>\n",
       "      <td>0.227611</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wes</th>\n",
       "      <td>-0.212439</td>\n",
       "      <td>0.482485</td>\n",
       "      <td>0.069546</td>\n",
       "      <td>-1.331693</td>\n",
       "      <td>0.124493</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jim</th>\n",
       "      <td>0.929621</td>\n",
       "      <td>-0.342086</td>\n",
       "      <td>0.134754</td>\n",
       "      <td>0.035518</td>\n",
       "      <td>-0.227611</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Travis</th>\n",
       "      <td>0.509531</td>\n",
       "      <td>-0.994348</td>\n",
       "      <td>-0.047835</td>\n",
       "      <td>1.099825</td>\n",
       "      <td>-0.151052</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               a         b         c         d         e\n",
       "Joe    -0.297092  0.511863 -0.021711  0.231868  0.026559\n",
       "Steve  -0.929621  0.342086 -0.134754 -0.035518  0.227611\n",
       "Wes    -0.212439  0.482485  0.069546 -1.331693  0.124493\n",
       "Jim     0.929621 -0.342086  0.134754  0.035518 -0.227611\n",
       "Travis  0.509531 -0.994348 -0.047835  1.099825 -0.151052"
      ]
     },
     "execution_count": 151,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def demean(arr):\n",
    "    return arr-arr.mean()\n",
    "\n",
    "demeaned=people.groupby(key).transform(demean)\n",
    "demeaned"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# 原文：https://blog.csdn.net/youngbit007/article/details/54288603\n",
    "# transform 与 apply 更多详自行百度"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 分位数和桶分析"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# cut and qcut与groupby结合起来，能轻松的对数据集的桶(bucket)或者分位数(quantile)分析。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 162,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>data1</th>\n",
       "      <th>data2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>-0.917789</td>\n",
       "      <td>-1.175998</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>-1.441893</td>\n",
       "      <td>-0.737888</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1.252216</td>\n",
       "      <td>-0.569244</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>-0.235966</td>\n",
       "      <td>-0.904568</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>-1.906962</td>\n",
       "      <td>-0.121977</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      data1     data2\n",
       "0 -0.917789 -1.175998\n",
       "1 -1.441893 -0.737888\n",
       "2  1.252216 -0.569244\n",
       "3 -0.235966 -0.904568\n",
       "4 -1.906962 -0.121977"
      ]
     },
     "execution_count": 162,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "frame=pd.DataFrame({'data1':np.random.randn(1000),\n",
    "                   'data2': np.random.randn(1000)})\n",
    "frame.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 168,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0     (-1.786, -0.0166]\n",
       "1     (-1.786, -0.0166]\n",
       "2      (-0.0166, 1.753]\n",
       "3     (-1.786, -0.0166]\n",
       "4      (-3.562, -1.786]\n",
       "5      (-0.0166, 1.753]\n",
       "6      (-0.0166, 1.753]\n",
       "7     (-1.786, -0.0166]\n",
       "8      (-0.0166, 1.753]\n",
       "9        (1.753, 3.522]\n",
       "10     (-0.0166, 1.753]\n",
       "11     (-0.0166, 1.753]\n",
       "12     (-0.0166, 1.753]\n",
       "13    (-1.786, -0.0166]\n",
       "14    (-1.786, -0.0166]\n",
       "15    (-1.786, -0.0166]\n",
       "16     (-0.0166, 1.753]\n",
       "17    (-1.786, -0.0166]\n",
       "18    (-1.786, -0.0166]\n",
       "19     (-0.0166, 1.753]\n",
       "Name: data1, dtype: category\n",
       "Categories (4, object): [(-3.562, -1.786] < (-1.786, -0.0166] < (-0.0166, 1.753] < (1.753, 3.522]]"
      ]
     },
     "execution_count": 168,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "factor=pd.cut(frame.data1,4)\n",
    "factor[:20]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 172,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>max</th>\n",
       "      <th>mean</th>\n",
       "      <th>min</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>data1</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>(-3.562, -1.786]</th>\n",
       "      <td>28.0</td>\n",
       "      <td>2.458871</td>\n",
       "      <td>-0.207221</td>\n",
       "      <td>-2.845501</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(-1.786, -0.0166]</th>\n",
       "      <td>454.0</td>\n",
       "      <td>2.478393</td>\n",
       "      <td>-0.079693</td>\n",
       "      <td>-3.378321</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(-0.0166, 1.753]</th>\n",
       "      <td>484.0</td>\n",
       "      <td>3.058291</td>\n",
       "      <td>-0.030417</td>\n",
       "      <td>-2.822678</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(1.753, 3.522]</th>\n",
       "      <td>34.0</td>\n",
       "      <td>1.494383</td>\n",
       "      <td>-0.054172</td>\n",
       "      <td>-1.442812</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   count       max      mean       min\n",
       "data1                                                 \n",
       "(-3.562, -1.786]    28.0  2.458871 -0.207221 -2.845501\n",
       "(-1.786, -0.0166]  454.0  2.478393 -0.079693 -3.378321\n",
       "(-0.0166, 1.753]   484.0  3.058291 -0.030417 -2.822678\n",
       "(1.753, 3.522]      34.0  1.494383 -0.054172 -1.442812"
      ]
     },
     "execution_count": 172,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def get_stats(group):\n",
    "    return {'min':group.min(),'max':group.max(),'count':group.count(),'mean':group.mean()}\n",
    "grouped=frame.data2.groupby(factor)\n",
    "grouped.apply(get_stats).unstack()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 174,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "data1                   \n",
       "(-3.562, -1.786]   count     28.000000\n",
       "                   max        2.458871\n",
       "                   mean      -0.207221\n",
       "                   min       -2.845501\n",
       "(-1.786, -0.0166]  count    454.000000\n",
       "                   max        2.478393\n",
       "                   mean      -0.079693\n",
       "                   min       -3.378321\n",
       "(-0.0166, 1.753]   count    484.000000\n",
       "                   max        3.058291\n",
       "                   mean      -0.030417\n",
       "                   min       -2.822678\n",
       "(1.753, 3.522]     count     34.000000\n",
       "                   max        1.494383\n",
       "                   mean      -0.054172\n",
       "                   min       -1.442812\n",
       "Name: data2, dtype: float64"
      ]
     },
     "execution_count": 174,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# unstack函数将数据从\"花括号结构（类似堆叠）\"成\"表格格式\"\n",
    "# 堆叠格式如下：\n",
    "grouped.apply(get_stats)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 159,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 这些都是长度相等的桶，要根据样本分为数据量大小相等的桶，使用qcut即可.\n",
    "\n",
    "# 长度相等的桶：区间大小相等 \n",
    "# 大小相等的桶：数据点数量相等"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 176,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>max</th>\n",
       "      <th>mean</th>\n",
       "      <th>min</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>data1</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100.0</td>\n",
       "      <td>2.458871</td>\n",
       "      <td>-0.188458</td>\n",
       "      <td>-2.845501</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>100.0</td>\n",
       "      <td>2.279129</td>\n",
       "      <td>-0.094244</td>\n",
       "      <td>-2.038445</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>100.0</td>\n",
       "      <td>2.478393</td>\n",
       "      <td>-0.054182</td>\n",
       "      <td>-2.948644</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>100.0</td>\n",
       "      <td>2.390311</td>\n",
       "      <td>-0.082986</td>\n",
       "      <td>-2.111568</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>100.0</td>\n",
       "      <td>2.446017</td>\n",
       "      <td>-0.070393</td>\n",
       "      <td>-3.378321</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>100.0</td>\n",
       "      <td>2.214205</td>\n",
       "      <td>-0.016592</td>\n",
       "      <td>-2.492703</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>100.0</td>\n",
       "      <td>2.607421</td>\n",
       "      <td>-0.151927</td>\n",
       "      <td>-2.822678</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>100.0</td>\n",
       "      <td>2.227739</td>\n",
       "      <td>0.074419</td>\n",
       "      <td>-2.673179</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>100.0</td>\n",
       "      <td>2.633283</td>\n",
       "      <td>0.011705</td>\n",
       "      <td>-2.264143</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>100.0</td>\n",
       "      <td>3.058291</td>\n",
       "      <td>-0.012806</td>\n",
       "      <td>-2.138321</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       count       max      mean       min\n",
       "data1                                     \n",
       "0      100.0  2.458871 -0.188458 -2.845501\n",
       "1      100.0  2.279129 -0.094244 -2.038445\n",
       "2      100.0  2.478393 -0.054182 -2.948644\n",
       "3      100.0  2.390311 -0.082986 -2.111568\n",
       "4      100.0  2.446017 -0.070393 -3.378321\n",
       "5      100.0  2.214205 -0.016592 -2.492703\n",
       "6      100.0  2.607421 -0.151927 -2.822678\n",
       "7      100.0  2.227739  0.074419 -2.673179\n",
       "8      100.0  2.633283  0.011705 -2.264143\n",
       "9      100.0  3.058291 -0.012806 -2.138321"
      ]
     },
     "execution_count": 176,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouping=pd.qcut(frame.data1,10,labels=False)#label=false即可值获取分位数的编号\n",
    "grouped=frame.data2.groupby(grouping)\n",
    "grouped.apply(get_stats).unstack()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python [conda root]",
   "language": "python",
   "name": "conda-root-py"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}
